﻿using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections;

namespace ZX.DAL.DataFactory
{
    /// <summary>
    /// 数据操作 基类
    /// </summary>
    internal abstract class  DataBase
    {

        #region 构造函数
        /// <summary>
        /// 构造一个数据访问对象
        /// </summary>
        /// <param name="connStr">连接字符串</param>
        /// <param name="dbProviderFactory">数据库提供者工厂</param>
        public DataBase(String connStr, DbProviderFactory dbProviderFactory)
        {   
            if (dbProviderFactory != null) _dbProviderFactory = dbProviderFactory;
            if (!String.IsNullOrEmpty(connStr))
            {
                Conn = _dbProviderFactory.CreateConnection();
                Conn.ConnectionString = connStr;
            }
           
        }
        /// <summary>
        /// 构造一个数据访问对象
        /// </summary>
        /// <param name="conn">连接</param>
        /// <param name="dbProviderFactory">数据库提供者工厂</param>
        public DataBase(DbConnection conn, DbProviderFactory dbProviderFactory)
        {
            Conn = conn;
            _dbProviderFactory = dbProviderFactory;
        }

        public DataBase()
        {
            Conn = _dbProviderFactory.CreateConnection();
            Conn.ConnectionString = this.ConntionString;
        }

        /// <summary>
        /// 是否已经释放
        /// </summary>
        private Boolean IsDisposed = false;
        /// <summary>
        /// 释放资源
        /// </summary>
        public virtual void Dispose()
        {
            if (IsDisposed) return;
            try
            {
                // 注意，没有Commit的数据，在这里将会被回滚
                //if (Trans != null) Rollback();
                if (Trans != null && Opened) Trans.Rollback();
                if (Conn != null) Close();
                IsDisposed = true;
            }
            catch (Exception ex)
            {
                //XTrace.WriteLine("执行" + this.GetType().FullName + "的Dispose时出错：" + ex.ToString());
            }
        }

        ~DataBase()
        {
            Dispose();
        }
        #endregion

        #region 数据连接/数据类型
        private string connectionString;
		public virtual string ConntionString 
		{
			get 
			{
                if (string.IsNullOrEmpty(connectionString))
                {
                    connectionString = ConfigurationManager.AppSettings["ConnectionString"]; 
                }
                return connectionString ; 
			}
			set 
			{
				connectionString = value ;
			}
		}
        /// <summary>
        /// 数据库类型 
        /// </summary>
        private string dbType = "Sqlite";
        public string DbType
        {
            get
            {
                 dbType = ConfigurationManager.AppSettings["DataType"];
                 return dbType;
            }
            set
            {
                if (value != string.Empty && value != null)
                {
                    dbType = value;
                }
                if (dbType == string.Empty || dbType == null)
                {
                    dbType = ConfigurationManager.AppSettings["DataType"];
                }
                if (dbType == string.Empty || dbType == null)
                {
                    dbType = "Sqlite";
                }
            }
        }
        #endregion

        #region 创建 Connection 和 Command
        public virtual DbConnection GetConnection(DbProviderFactory dpf)
        {
            return dpf.CreateConnection();
        }

        public abstract DbCommand GetCommand(string Sql, DbConnection iConn);

        /// <summary>
        /// 获取Command对象
        /// </summary>
        /// <returns></returns>
        public abstract DbCommand GetCommand();

        /// <summary>
        /// DataAdapter
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="iConn"></param>
        /// <returns></returns>
        public abstract DataAdapter GetAdapater(string Sql, DbConnection iConn);
        public abstract DataAdapter GetAdapater();
        public abstract DataAdapter GetAdapater(DbCommand iCmd);

        #endregion




        #region  执行简单SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SqlString)
        {
            using (DbConnection iConn = Conn)
            {
                using (DbCommand iCmd = GetCommand(SqlString, iConn))
                {
                    //try
                    //{		
                    iConn.Open();
                    int rows = iCmd.ExecuteNonQuery();
                    return rows;
                    //}
                    //catch(System.Exception E)
                    //{					
                    //    iConn.Close();
                    //    throw new Exception(E.Message);
                    //}
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (DbConnection iConn = Conn)
            {
                iConn.Open();
                DbCommand dbCmd = GetCommand();
                dbCmd.Connection = iConn;
                DbTransaction DbTran = iConn.BeginTransaction();
                dbCmd.Transaction = DbTran;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            dbCmd.CommandText = strsql;
                            dbCmd.ExecuteNonQuery();
                        }
                    }
                    DbTran.Commit();
                }
                catch (System.Exception E)
                {
                    DbTran.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        /// <summary>
        ///  执行Sql事务
        /// </summary>
        /// <param name="strsql">Sql Command</param>
        public void ExecuteSqlTran(string strsql)
        {
            using (DbConnection iConn = Conn)
            {
                iConn.Open();
                DbCommand iCmd = GetCommand();
                iCmd.Connection = iConn;
                DbTransaction iDbTran = iConn.BeginTransaction();
                iCmd.Transaction = iDbTran;
                try
                {
                    iCmd.CommandText = strsql;
                    iCmd.ExecuteNonQuery();
                    iDbTran.Commit();
                }
                catch (System.Exception E)
                {
                    iDbTran.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }


        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SqlString)
        {
            using (DbConnection iConn =Conn)
            {
                using (DbCommand iCmd = GetCommand(SqlString, iConn))
                {
                    try
                    {
                        iConn.Open();
                        object obj = iCmd.ExecuteScalar();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Exception e)
                    {
                        iConn.Close();
                        throw new Exception(e.Message);
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns>SqlDataReader</returns>
        public IDataAdapter ExecuteReader(string strSQL)
        {
            using ( DbConnection iConn = Conn)
            {
                try
                {
                    iConn.Open();
                    System.Data.IDataAdapter iAdapter = this.GetAdapater(strSQL, iConn);
                    return iAdapter;
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    iConn.Close();
                }
            }
        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string SqlString)
        {
            using (DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand(SqlString, iConn);
                DataSet ds = new DataSet();
                try
                {
                    iConn.Open();
                    System.Data.IDataAdapter iAdapter = this.GetAdapater(SqlString, iConn);
                    iAdapter.Fill(ds);
                    return ds;
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }


        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public void WriteToXml(string SqlString, string XmlPath)
        {
            try
            {
                Query(SqlString).WriteXml(XmlPath);
            }
            catch (System.Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// 获取查询结果的第一行第一列
        /// </summary>
        /// <param name="SqlString">SQL语句</param>
        /// <returns>返回查询行数</returns>
        public int ExecuteScalar(string SqlString)
        {
            using (DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand(SqlString, iConn);
                int result = 0;
                try
                {
                    iConn.Open();
                    //如果查询结果不为空
                    if (null != iCmd.ExecuteScalar())
                    {
                        result = (int)iCmd.ExecuteScalar();
                    }
                }
                catch (System.Exception E)
                {
                    iConn.Close();
                    throw new Exception(E.Message);
                }
                return result;
            }
        }

        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="SqlString">查询语句</param>
        /// <returns>DataTable </returns>
        public DataTable ExecuteQuery(string SqlString)
        {
            using (DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand(SqlString, Conn);
                DataSet ds = new DataSet();
                try
                {
                    System.Data.IDataAdapter iReader = this.GetAdapater(SqlString, iConn);
                    iReader.Fill(ds);
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message);
                }
                return ds.Tables[0];
            }
        }


        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="SqlString">查询语句</param>
        /// <returns>DataTable </returns>
        public DataSet GetDataSet(string SqlString)
        {
            using (DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand(SqlString, iConn);
                DataSet ds = new DataSet();
                try
                {
                    System.Data.IDataAdapter iReader = this.GetAdapater(SqlString, iConn);
                    iReader.Fill(ds);
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message);
                }
                return ds;
            }
        }


        /// <summary>
        /// 获取表数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetDTByTable(string tableName)
        {
            return ExecuteQuery("Select * from " + tableName);
        }

        /// <summary>
        /// 获取表数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetDTByTable(string tableName, string condition)
        {
            return ExecuteQuery("Select * from " + tableName + " where codition");
        }


        /// <summary>
        /// 获取表结构
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetDTStrucByTable(string tableName)
        {
            return ExecuteQuery("Select * from " + tableName + " where 1=0");
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public int GetRowCount(string TableName, string Condition)
        {

            string SqlString = "select count(*) as TotalRows from " + TableName + (!string.IsNullOrEmpty(Condition) ? " where " + Condition : "");
            using (DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand(SqlString, iConn);
                DataSet ds = new DataSet();
                try
                {
                    System.Data.IDataAdapter iReader = this.GetAdapater(SqlString, iConn);
                    iReader.Fill(ds);
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message);
                }
                return (int)ds.Tables[0].Rows[0].ItemArray[0];
            }
        }

        /// <summary>
        /// 执行查询语句
        /// </summary>
        /// <param name="SqlString">查询语句</param>
        /// <returns>DataTable </returns>
        public DataTable ExecuteQuery(string SqlString, string Proc)
        {
            using ( DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand(SqlString, iConn);
                iCmd.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                try
                {
                    System.Data.IDataAdapter iReader = this.GetAdapater(SqlString, iConn);
                    iReader.Fill(ds);
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message);
                }
                return ds.Tables[0];
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
        public DataView ExeceuteDataView(string Sql)
        {
            using ( DbConnection iConn = Conn )
            {
                DbCommand iCmd = GetCommand(Sql, iConn);
                DataSet ds = new DataSet();
                try
                {
                    System.Data.IDataAdapter iReader = this.GetAdapater(Sql, iConn);
                    iReader.Fill(ds);
                    return ds.Tables[0].DefaultView;
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message);
                }

            }
        }

        #endregion

        #region 执行带参数的SQL语句
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString, params IDataParameter[] iParms)
        {
            using ( DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand();
                {
                    try
                    {
                        PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
                        int rows = iCmd.ExecuteNonQuery();
                        iCmd.Parameters.Clear();
                        return rows;
                    }
                    catch (System.Exception E)
                    {
                        throw new Exception(E.Message);
                    }
                }
            }
        }


        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
        public void ExecuteSqlTran(Hashtable SQLStringList)
        {
            using ( DbConnection iConn = Conn)
            {
                iConn.Open();
                using (DbTransaction iTrans = iConn.BeginTransaction())
                {
                     DbCommand iCmd = GetCommand();
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)
                        {
                            string cmdText = myDE.Key.ToString();
                            IDataParameter[] iParms = (IDataParameter[])myDE.Value;
                            PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms);
                            int val = iCmd.ExecuteNonQuery();
                            iCmd.Parameters.Clear();
                        }
                        iTrans.Commit();
                    }
                    catch
                    {
                        iTrans.Rollback();
                        throw;
                    }
                }
            }
        }


        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public object GetSingle(string SQLString, params IDataParameter[] iParms)
        {
            using (DbConnection iConn = Conn )
            {
                DbCommand iCmd = GetCommand();
                {
                    try
                    {
                        PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
                        object obj = iCmd.ExecuteScalar();
                        iCmd.Parameters.Clear();
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                        {
                            return null;
                        }
                        else
                        {
                            return obj;
                        }
                    }
                    catch (System.Exception e)
                    {
                        throw new Exception(e.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回SqlDataReader
        /// </summary>
        /// <param name="strSQL">查询语句</param>
        /// <returns> IDataReader </returns>
        public IDataReader ExecuteReader(string SQLString, params IDataParameter[] iParms)
        {
            using (DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand();
                try
                {
                    PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
                    System.Data.IDataReader iReader = iCmd.ExecuteReader();
                    iCmd.Parameters.Clear();
                    return iReader;
                }
                catch (System.Exception e)
                {
                    throw new Exception(e.Message);
                }
                finally
                {
                    iCmd.Dispose();
                    iConn.Close();
                }
            }

        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string SQLString, params IDataParameter[] iParms)
        {
            using (DbConnection iConn = Conn)
            {
                DbCommand iCmd = GetCommand();
                PrepareCommand(out iCmd, iConn, null, SQLString, iParms);

                try
                {
                    IDataAdapter iAdapter = this.GetAdapater(SQLString, iConn);
                    DataSet ds = new DataSet();
                    iAdapter.Fill(ds);
                    iCmd.Parameters.Clear();
                    return ds;
                }
                catch (System.Exception ex)
                {
                    throw new Exception(ex.Message);
                }

            }

        }


        /// <summary>
        /// 初始化Command
        /// </summary>
        /// <param name="iCmd"></param>
        /// <param name="iConn"></param>
        /// <param name="iTrans"></param>
        /// <param name="cmdText"></param>
        /// <param name="iParms"></param>
        private void PrepareCommand(out DbCommand iCmd, DbConnection iConn, DbTransaction iTrans, string cmdText, IDataParameter[] iParms)
        {
            if (iConn.State != ConnectionState.Open)
                iConn.Open();
            iCmd = this.GetCommand();
            iCmd.Connection = iConn;
            iCmd.CommandText = cmdText;
            if (iTrans != null)
                iCmd.Transaction = iTrans;
            iCmd.CommandType = CommandType.Text;//cmdType;
            if (iParms != null)
            {
                foreach (IDataParameter parm in iParms)
                    iCmd.Parameters.Add(parm);
            }
        }

        #endregion

        #region 存储过程操作

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlDataReader</returns>
        public  IDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using ( DbConnection iConn = Conn)
            {
                IDataReader returnReader;
                iConn.Open();
                DbCommand iCmd = GetCommand();
                iCmd.CommandType = CommandType.StoredProcedure;
                returnReader = iCmd.ExecuteReader();
                return returnReader;
            }
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="tableName">DataSet结果中的表名</param>
        /// <returns>DataSet</returns>
        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, bool bl)
        {
            if (!bl)
            {
                return null;
            }

            using ( DbConnection iConn = Conn)
            {
                DataSet dataSet = new DataSet();
                iConn.Open();
                System.Data.IDataAdapter iDA = this.GetAdapater();
                iDA = this.GetAdapater(BuildQueryCommand(iConn, storedProcName, parameters));
                iDA.Fill(dataSet);
                iConn.Close();
                return dataSet;
            }
        }

        /// <summary>
        /// 执行无返回值的存储过程
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="bl"></param>
        public void RunProcedureNoQuery(string storedProcName, IDataParameter[] parameters, bool bl)
        {
            if (!bl)
            {
                return;
            }

            using ( DbConnection iConn = Conn)
            {
                iConn.Open();
                try
                {
                    BuildQueryCommand(iConn, storedProcName, parameters).ExecuteNonQuery();
                }
                catch (System.Exception e)
                {
                    throw e;
                }
            }
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private DbCommand BuildQueryCommand(DbConnection iConn, string storedProcName, IDataParameter[] parameters)
        {

            DbCommand iCmd = GetCommand(storedProcName, iConn);
            iCmd.CommandType = CommandType.StoredProcedure;
            if (parameters == null)
            {
                return iCmd;
            }
            foreach (IDataParameter parameter in parameters)
            {
                iCmd.Parameters.Add(parameter);
            }
            return iCmd;
        }

        private DbCommand BuildQueryCommand(DbConnection iConn, string SqlComm)
        {
            DbCommand iCmd = GetCommand(SqlComm, iConn);
            iCmd.CommandType = CommandType.Text;
            return iCmd;
        }
        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (DbConnection iConn = Conn )
            {
                int result;
                iConn.Open();
                DbCommand iCmd = BuildQueryCommand(iConn, storedProcName, parameters);
                rowsAffected = iCmd.ExecuteNonQuery();
                result = (int)iCmd.Parameters["ReturnValue"].Value;
                iConn.Close();
                return result;
            }
        }

        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public string RunProcedureReturnString(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using ( DbConnection iConn = Conn )
            {
                string result;
                iConn.Open();
                DbCommand iCmd = BuildQueryCommand(iConn, storedProcName, parameters);
                rowsAffected = iCmd.ExecuteNonQuery();

                result = (String)parameters[parameters.Length - 1].Value;
                iConn.Close();
                return result;
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="SqlCommand">SQL命令行，或者存储过程</param>
        /// <returns></returns>
        public int RunSqlCommand(string SqlCommand)
        {
            using (DbConnection iConn = Conn)
            {
                int result;
                iConn.Open();
                DbCommand iCmd = BuildQueryCommand(iConn, SqlCommand);
                result = iCmd.ExecuteNonQuery();
                //result = (int)iCmd.Parameters["ReturnValue"];
                iConn.Close();
                return result;
            }
        }

        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        //		private IDbCommand BuildIntCommand(IDbConnection iConn,string storedProcName, IDataParameter[] parameters)
        //		{
        //			IDbCommand iCmd = BuildQueryCommand(iConn,storedProcName, parameters );
        //			iCmd.Parameters.Add( new SqlParameter ( "ReturnValue",
        //				SqlDbType.Int,4,ParameterDirection.ReturnValue,
        //				false,0,0,string.Empty,DataRowVersion.Default,null ));
        //			return command;
        //		}
        #endregion	

        #region 属性
        private readonly DbProviderFactory _dbProviderFactory;
        private DbConnection _Conn;
        /// <summary>
        /// 数据连接对象。
        /// </summary>
        protected DbConnection Conn
        {
            get { return _Conn; }
            set { _Conn = value; }
        }
        #endregion

        #region 事务
        private DbTransaction _Trans;
        /// <summary>
        /// 数据库事务
        /// </summary>
        protected DbTransaction Trans
        {
            get { return _Trans; }
            set { _Trans = value; }
        }

        /// <summary>
        /// 事务计数。
        /// 当且仅当事务计数等于1时，才提交或回滚。
        /// </summary>
        private Int32 TransactionCount = 0;

        /// <summary>
        /// 开始事务
        /// </summary>
        /// <returns></returns>
        public DbTransaction BeginTransaction()
        {
            //if (Debug) WriteLog("开始事务：{0}", ID);

            TransactionCount++;
            if (TransactionCount > 1) return Trans;

            try
            {
                if (!Opened) Open();
                Trans = Conn.BeginTransaction();
                TransactionCount = 1;
                return Trans;
            }
            catch (Exception ex)
            {
                throw OnException(ex);
            }
        }
        #endregion

        #region 打开/关闭
        private Boolean _IsAutoClose = true;
        /// <summary>
        /// 是否自动关闭。
        /// 启用事务后，该设置无效。
        /// 在提交或回滚事务时，如果IsAutoClose为true，则会自动关闭
        /// </summary>
        public Boolean IsAutoClose
        {
            get { return _IsAutoClose; }
            set { _IsAutoClose = value; }
        }

        /// <summary>
        /// 连接是否已经打开
        /// </summary>
        public Boolean Opened
        {
            get { return Conn != null && Conn.State != ConnectionState.Closed; }
        }

        /// <summary>
        /// 打开
        /// </summary>
        public virtual void Open()
        {
            if (Conn != null && Conn.State == ConnectionState.Closed)
            {
                try { Conn.Open(); }
                catch (Exception ex)
                {
                    //XTrace.WriteLine("执行" + this.GetType().FullName + "的Open时出错：" + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 关闭
        /// </summary>
        public virtual void Close()
        {
            if (Conn != null && Conn.State != ConnectionState.Closed)
            {
                try { Conn.Close(); }
                catch (Exception ex)
                {
                    //XTrace.WriteLine("执行" + this.GetType().FullName + "的Close时出错：" + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 自动关闭。
        /// 启用事务后，不关闭连接。
        /// 在提交或回滚事务时，如果IsAutoClose为true，则会自动关闭
        /// </summary>
        public void AutoClose()
        {
            if (IsAutoClose && Trans == null && Opened) Close();
        }

        /// <summary>
        /// 当异常发生时触发。关闭数据库连接，或者返还连接到连接池。
        /// </summary>
        /// <param name="ex"></param>
        /// <returns></returns>
        protected virtual Exception OnException(Exception ex)
        {
            if (Trans == null && Opened) Close(); // 强制关闭数据库
            //return new XException("内部数据库实体" + this.GetType().FullName + "异常，执行" + Environment.StackTrace + "方法出错！", ex);
            String err = "内部数据库实体" + DbType.ToString() + "异常，执行方法出错！" + Environment.NewLine + ex.Message;
            if (ex != null)
                return new Exception(err, ex);
            else
                return new Exception(err);
        }

        /// <summary>
        /// 当异常发生时触发。关闭数据库连接，或者返还连接到连接池。
        /// </summary>
        /// <param name="ex"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected virtual Exception OnException(Exception ex, String sql)
        {
            if (Trans == null && Opened) Close(); // 强制关闭数据库
            //return new XException("内部数据库实体" + this.GetType().FullName + "异常，执行" + Environment.StackTrace + "方法出错！", ex);
            String err = "内部数据库实体" + DbType.ToString() + "异常，执行方法出错！" + Environment.NewLine;
            if (!String.IsNullOrEmpty(sql)) err += "SQL语句：" + sql + Environment.NewLine;
            err += ex.Message;
            if (ex != null)
                return new Exception(err, ex);
            else
                return new Exception(err);
        }
        #endregion

        #region Execl 操作
        /// <summary>
        /// 读取数据
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="Rows"></param>
        /// <returns></returns>
        //public DataTable ReadEcxel(string FileName, int Rows)
        //{
        //    DataTable dt = new DataTable();
        //    // 获取文件绝对路径
        //    string _filePath = FileName;
        //    // 如果Excel中的第一列为栏名,则写成
        //    string sConnectionString = "";
        //    if (Rows == 1)
        //    {
        //        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filePath + ";Extended Properties=\"Excel 8.0;HDR=YES\"";
        //    }
        //    else
        //    {
        //        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filePath + ";Extended Properties=\"Excel 8.0;HDR=NO\"";
        //    }
        //    OleDbConnection cn = new OleDbConnection();
        //    cn.ConnectionString = sConnectionString;
        //    cn.Open();
        //    string strSQL = "SELECT * FROM [Sheet1$]";
        //    System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSQL, cn);
        //    da.Fill(dt);
        //    cn.Close();
        //    return dt;
        //}

        #endregion
    } 
}
